# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt
import copy
import functools
import math
import re
import frappe
from frappe import _
from frappe.query_builder.functions import Max, Min, Sum
from frappe.utils import add_days, add_months, cint, cstr, flt, formatdate, get_first_day, getdate
from pypika.terms import ExistsCriterion
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
    get_accounting_dimensions,
    get_dimension_with_children,
)
from erpnext.accounts.report.utils import convert_to_presentation_currency, get_currency
from erpnext.accounts.utils import get_fiscal_year

def get_period_list(
    from_fiscal_year,
    to_fiscal_year,
    period_start_date,
    period_end_date,
    filter_based_on,
    periodicity,
    accumulated_values=False,
    company=None,
    reset_period_on_fy_change=True,
    ignore_fiscal_year=False,
    include_yoy=False  # 新增：是否包含同比周期
):
    """Get a list of dict with period info, including YoY periods if needed"""
    if filter_based_on == "Fiscal Year":
        fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year)
        validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year)
        year_start_date = getdate(fiscal_year.year_start_date)
        year_end_date = getdate(fiscal_year.year_end_date)
    else:
        validate_dates(period_start_date, period_end_date)
        year_start_date = getdate(period_start_date)
        year_end_date = getdate(period_end_date)

    months_to_add = {"Yearly": 12, "Half-Yearly": 6, "Quarterly": 3, "Monthly": 1}[periodicity]
    period_list = []
    start_date = year_start_date
    months = get_months(year_start_date, year_end_date)

    for i in range(cint(math.ceil(months / months_to_add))):
        period = frappe._dict({"from_date": start_date})
        if i == 0 and filter_based_on == "Date Range":
            to_date = add_months(get_first_day(start_date), months_to_add)
        else:
            to_date = add_months(start_date, months_to_add)
        start_date = to_date
        to_date = add_days(to_date, -1)

        if to_date <= year_end_date:
            period.to_date = to_date
        else:
            period.to_date = year_end_date

        if not ignore_fiscal_year:
            period.to_date_fiscal_year = get_fiscal_year(period.to_date, company=company)[0]
            period.from_date_fiscal_year_start_date = get_fiscal_year(period.from_date, company=company)[1]
        
        # 新增：计算同比周期（上一年同期）
        if include_yoy:
            period.yoy_from_date = add_months(period.from_date, -12)
            period.yoy_to_date = add_months(period.to_date, -12)
            period.yoy_key = f"yoy_{period.to_date.strftime('%b_%Y').lower()}"
        
        period_list.append(period)
        if period.to_date == year_end_date:
            break

    # 补充周期标签和键
    for opts in period_list:
        key = opts["to_date"].strftime("%b_%Y").lower()
        if periodicity == "Monthly" and not accumulated_values:
            label = formatdate(opts["to_date"], "MMM YYYY")
        else:
            if not accumulated_values:
                label = get_label(periodicity, opts["from_date"], opts["to_date"])
            else:
                if reset_period_on_fy_change:
                    label = get_label(periodicity, opts.from_date_fiscal_year_start_date, opts["to_date"])
                else:
                    label = get_label(periodicity, period_list[0].from_date, opts["to_date"])
        
        # 新增：同比周期标签
        if include_yoy:
            opts.yoy_label = f"{label} (YoY)"
        
        opts.update({
            "key": key.replace(" ", "_").replace("-", "_"),
            "label": label,
            "year_start_date": year_start_date,
            "year_end_date": year_end_date,
        })

    return period_list

def get_fiscal_year_data(from_fiscal_year, to_fiscal_year):
    from_year_start_date = frappe.get_cached_value("Fiscal Year", from_fiscal_year, "year_start_date")
    to_year_end_date = frappe.get_cached_value("Fiscal Year", to_fiscal_year, "year_end_date")
    fy = frappe.qb.DocType("Fiscal Year")
    query = (
        frappe.qb.from_(fy)
        .select(Min(fy.year_start_date).as_("year_start_date"), Max(fy.year_end_date).as_("year_end_date"))
        .where(fy.year_start_date >= from_year_start_date)
        .where(fy.year_end_date <= to_year_end_date)
    )
    fiscal_year = query.run(as_dict=True)
    return fiscal_year[0] if fiscal_year else {}

def validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year):
    if not fiscal_year.get("year_start_date") or not fiscal_year.get("year_end_date"):
        frappe.throw(_("Start Year and End Year are mandatory"))
    if getdate(fiscal_year.get("year_end_date")) < getdate(fiscal_year.get("year_start_date")):
        frappe.throw(_("End Year cannot be before Start Year"))

def validate_dates(from_date, to_date):
    if not from_date or not to_date:
        frappe.throw(_("From Date and To Date are mandatory"))
    if to_date < from_date:
        frappe.throw(_("To Date cannot be less than From Date"))

def get_months(start_date, end_date):
    diff = (12 * end_date.year + end_date.month) - (12 * start_date.year + start_date.month)
    return diff + 1

def get_label(periodicity, from_date, to_date):
    if periodicity == "Yearly":
        if formatdate(from_date, "YYYY") == formatdate(to_date, "YYYY"):
            label = formatdate(from_date, "YYYY")
        else:
            label = formatdate(from_date, "YYYY") + "-" + formatdate(to_date, "YYYY")
    else:
        label = formatdate(from_date, "MMM YY") + "-" + formatdate(to_date, "MMM YY")
    return label

def get_data(
    company,
    root_type,
    balance_must_be,
    period_list,
    filters=None,
    accumulated_values=1,
    only_current_fiscal_year=True,
    ignore_closing_entries=False,
    ignore_accumulated_values_for_fy=False,
    total=True,
    include_yoy=False,  # 新增：是否计算同比
    include_budget=False  # 新增：是否计算预算对比
):
    accounts = get_accounts(company, root_type)
    if not accounts:
        return None
    accounts, accounts_by_name, parent_children_map = filter_accounts(accounts)
    company_currency = get_appropriate_currency(company, filters)

    # 新增：获取预算数据
    budget_data = get_budget_data(company, accounts, period_list) if include_budget else {}

    # 获取当前周期和同比周期的总账数据
    gl_entries_by_account = {}
    for root in frappe.db.sql(
        """select lft, rgt from tabAccount
        where root_type=%s and ifnull(parent_account, '') = ''""",
        root_type,
        as_dict=1,
    ):
        # 当前周期数据
        set_gl_entries_by_account(
            company,
            period_list[0]["year_start_date"] if only_current_fiscal_year else None,
            period_list[-1]["to_date"],
            filters,
            gl_entries_by_account,
            root.lft,
            root.rgt,
            root_type=root_type,
            ignore_closing_entries=ignore_closing_entries,
        )
        # 同比周期数据
        if include_yoy:
            set_gl_entries_by_account(
                company,
                period_list[0]["yoy_from_date"] if only_current_fiscal_year else None,
                period_list[-1]["yoy_to_date"],
                filters,
                gl_entries_by_account,
                root.lft,
                root.rgt,
                root_type=root_type,
                ignore_closing_entries=ignore_closing_entries,
                is_yoy=True  # 标记为同比数据
            )

    # 计算当前周期、同比、预算数据
    calculate_values(
        accounts_by_name,
        gl_entries_by_account,
        period_list,
        accumulated_values,
        ignore_accumulated_values_for_fy,
        include_yoy,
        include_budget,
        budget_data
    )

    accumulate_values_into_parents(accounts, accounts_by_name, period_list, include_yoy, include_budget)
    out = prepare_data(
        accounts,
        balance_must_be,
        period_list,
        company_currency,
        accumulated_values=filters.accumulated_values if filters else False,
        include_yoy=include_yoy,
        include_budget=include_budget
    )
    out = filter_out_zero_value_rows(out, parent_children_map)
    if out and total:
        add_total_row(out, root_type, balance_must_be, period_list, company_currency, include_yoy, include_budget)
    return out

def get_appropriate_currency(company, filters=None):
    if filters and filters.get("presentation_currency"):
        return filters["presentation_currency"]
    else:
        return frappe.get_cached_value("Company", company, "default_currency")

def get_budget_data(company, accounts, period_list):
    """获取账户的预算数据"""
    budget_data = {}
    account_names = [d.name for d in accounts]
    for period in period_list:
        budget_entries = frappe.db.get_list(
            "Budget Entry",
            filters={
                "company": company,
                "account": ("in", account_names),
                "from_date": ("<=", period.to_date),
                "to_date": (">=", period.from_date)
            },
            fields=["account", "sum(amount) as total_amount"],
            group_by="account"
        )
        for entry in budget_entries:
            budget_data.setdefault(entry.account, {})[period.key] = flt(entry.total_amount)
    return budget_data

def set_gl_entries_by_account(
    company, from_date, to_date, filters, gl_entries_by_account, lft, rgt, root_type,
    ignore_closing_entries=False, is_yoy=False  # 新增：标记同比数据
):
    """获取总账数据（支持同比）"""
    conditions = get_gl_entries_conditions(company, from_date, to_date, filters, ignore_closing_entries)
    gl_entries = frappe.db.sql("""
        select account, posting_date, debit, credit, fiscal_year
        from `tabGL Entry`
        where docstatus < 2 and account in (
            select name from tabAccount where lft >= %s and rgt <= %s and company = %s
        ) {conditions}
    """.format(conditions=conditions), (lft, rgt, company), as_dict=1)

    # 存储数据（同比数据用特殊键区分）
    for entry in gl_entries:
        key = f"yoy_{entry.account}" if is_yoy else entry.account
        if key not in gl_entries_by_account:
            gl_entries_by_account[key] = []
        gl_entries_by_account[key].append(entry)

def get_gl_entries_conditions(company, from_date, to_date, filters, ignore_closing_entries):
    """构建总账查询条件"""
    conditions = []
    if from_date:
        conditions.append(f"posting_date >= '{from_date}'")
    if to_date:
        conditions.append(f"posting_date <= '{to_date}'")
    if ignore_closing_entries:
        conditions.append("voucher_type != 'Period Closing Voucher'")
    if filters and filters.get("project"):
        conditions.append(f"project = '{filters.get('project')}'")
    return "and " + " and ".join(conditions) if conditions else ""

def calculate_values(
    accounts_by_name,
    gl_entries_by_account,
    period_list,
    accumulated_values,
    ignore_accumulated_values_for_fy,
    include_yoy,
    include_budget,
    budget_data
):
    """计算当前周期、同比、预算差异"""
    # 计算当前周期数据
    for account, entries in gl_entries_by_account.items():
        if account.startswith("yoy_"):
            continue  # 同比数据单独处理
        d = accounts_by_name.get(account)
        if not d:
            continue
        for entry in entries:
            for period in period_list:
                if entry.posting_date <= period.to_date and (accumulated_values or entry.posting_date >= period.from_date):
                    d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)

    # 计算同比数据
    if include_yoy:
        for account, entries in gl_entries_by_account.items():
            if not account.startswith("yoy_"):
                continue
            original_account = account.replace("yoy_", "")
            d = accounts_by_name.get(original_account)
            if not d:
                continue
            for entry in entries:
                for period in period_list:
                    if entry.posting_date <= period.yoy_to_date and entry.posting_date >= period.yoy_from_date:
                        d[period.yoy_key] = d.get(period.yoy_key, 0.0) + flt(entry.debit) - flt(entry.credit)

    # 计算预算差异
    if include_budget:
        for d in accounts_by_name.values():
            for period in period_list:
                actual = d.get(period.key, 0.0)
                budget = budget_data.get(d.name, {}).get(period.key, 0.0)
                d[f"budget_{period.key}"] = actual - budget  # 差异=实际-预算

def accumulate_values_into_parents(accounts, accounts_by_name, period_list, include_yoy, include_budget):
    """将子账户数据汇总到父账户"""
    for d in reversed(accounts):
        if d.parent_account:
            parent = accounts_by_name[d.parent_account]
            for period in period_list:
                # 当前周期
                parent[period.key] = parent.get(period.key, 0.0) + d.get(period.key, 0.0)
                # 同比
                if include_yoy:
                    parent[period.yoy_key] = parent.get(period.yoy_key, 0.0) + d.get(period.yoy_key, 0.0)
                # 预算差异
                if include_budget:
                    parent[f"budget_{period.key}"] = parent.get(f"budget_{period.key}", 0.0) + d.get(f"budget_{period.key}", 0.0)

def prepare_data(accounts, balance_must_be, period_list, company_currency, accumulated_values, include_yoy, include_budget):
    """准备输出数据，包含同比和预算列"""
    data = []
    year_start_date = period_list[0]["year_start_date"].strftime("%Y-%m-%d")
    year_end_date = period_list[-1]["year_end_date"].strftime("%Y-%m-%d")
    for d in accounts:
        row = frappe._dict({
            "account": _(d.name),
            "parent_account": _(d.parent_account) if d.parent_account else "",
            "indent": flt(d.indent),
            "year_start_date": year_start_date,
            "year_end_date": year_end_date,
            "currency": company_currency,
            "include_in_gross": d.include_in_gross,
            "account_type": d.account_type,
            "is_group": d.is_group,
            "opening_balance": d.get("opening_balance", 0.0) * (1 if balance_must_be == "Debit" else -1),
            "account_name": f"{_(d.account_number)} - {_(d.account_name)}" if d.account_number else _(d.account_name)
        })
        # 添加周期数据
        for period in period_list:
            row[period.key] = d.get(period.key, 0.0)
            # 同比列
            if include_yoy:
                row[period.yoy_key] = d.get(period.yoy_key, 0.0)
            # 预算差异列
            if include_budget:
                row[f"budget_{period.key}"] = d.get(f"budget_{period.key}", 0.0)
        data.append(row)
    return data


